package imgtagcounter;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
 * This class is in charge of create, connect, send/receive data from 
 * Imgtagcounter. It will create a database if isn't created before or just 
 * connect and update/receive all data, it can also request queries from Database. 
 * @author Oleg Ruchlis & Marcelo Korn
 **/
public class DatabaseUrl {
    
    /** Creates a new instance of DatabaseUrl(default constractor)
    *   It will build an statistics database, it will call the constructor which
    *   receives name of database.
    **/
    public DatabaseUrl() throws IOException {
    }    
    /**
     * Constructor
     * @param pages - string that has the name of database.
     * @throws IOException when can't load the properties file.
     * @throws ClassNotFoundException when receives a wrong class name.
     * related to the connection.
     */
    public DatabaseUrl(String pages) throws IOException, ClassNotFoundException{
         this.dbName = pages;
         //set all conf for the Database(drivers and creation
         setDBSystemDir();
         try{
            dbProperties = loadDBProperties();
         }
         catch(IOException ex){
             throw ex;
         }
         String driverName = dbProperties.getProperty("derby.driver"); 
         
         try{
             loadDatabaseDriver(driverName);
         }catch(ClassNotFoundException ex){
             throw ex;
         }
         //if didn't exist, so create it
         if(!dbExists())
         {
            createDatabase();
         }
        
    }
     /**
     * This method will save record into the database, if there is no Url to 
     * insert into the database, return -1.
     * @param Url String with the URL address.
     * @param numberImages integer which represents number of images.
     * @throws Exception re-throws the sql error message with number of error
     * related to the connection.
     */
    public int saveRecord(String Url, int numberImages){
        int id = -1;
        //if there is no database key, retrun -1
        if (Url == null)
        {
            return id;
        }
        //connection saving data
        try {
            stmtSaveNewRecord.clearParameters();
            stmtSaveNewRecord.setString(1,Url); 
            stmtSaveNewRecord.setInt(2,numberImages);             
            stmtSaveNewRecord.executeUpdate();
            ResultSet results = stmtSaveNewRecord.getGeneratedKeys();
            if (results.next()) {
                id = results.getInt(1);
            }            
        } catch(SQLException sqle) {
            System.out.println("Sqle error number : " + sqle.getErrorCode()+
                               "\nError type: "         + sqle.getMessage());           
        }
        return id;
    }
        /**
     * This method creates the database, return bool if database was created 
     * @throws Exception re-throws the sql error message with number of error
     * related to the connection.
     */
    private boolean createDatabase() {
        boolean bCreated = false;
        Connection dbConnection1 = null;
        //get database to connect
        String dbUrl = getDatabaseUrl();        
        dbProperties.put("create", "true");        
        try {
            dbConnection1 = DriverManager.getConnection(dbUrl, dbProperties);
            bCreated = createTables(dbConnection1);
        } catch (SQLException ex) {}
        //remove the creation preperties(was created already)
        dbProperties.remove("create");
        return bCreated;
    }
     /**
     * This  Method check if database exists, getting location and file
     * directory returning boolean if was exists.
     */
    private boolean dbExists() {
        boolean bExists = false;
        String dbLocation = getDatabaseLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()) {
            bExists = true;
        }
        return bExists;
    }
        /**
        * Get database location 
        **/
    public String getDatabaseLocation() {
        String dbLocation = System.getProperty("derby.system.home")
                                                    + "\\" + dbName;
        return dbLocation;
    }
        /**
        * Set directory of system database
        **/
    private void setDBSystemDir() {
        // decide on the db system directory
        String userHomeDir = System.getProperty("user.home", ".");
        //String systemDir = userHomeDir ;
        System.setProperty("derby.system.home", userHomeDir);
        
        // create the db system directory
        File fileSystemDir = new File(userHomeDir);
        fileSystemDir.mkdir();
    }   
     /**
     * Load all properties from database
     * @throws Exception type message and local error.
     */
    private Properties loadDBProperties() throws IOException {
        InputStream dbPropInputStream = null;
        dbPropInputStream = DatabaseUrl.class.getResourceAsStream("Configuration.properties");
        dbProperties = new Properties();
        try {
            dbProperties.load(dbPropInputStream);
        } catch (IOException ex) {
           throw ex;
        }
        return dbProperties;
    }     
    /**
     * This method load the driver to connect to the database
     * @param driverName string with the driver name that will
     * be used in the connection
     * @throws ClassNotFoundException with kind of error if didnt load driver .
     */
    private void loadDatabaseDriver(String driverName)throws ClassNotFoundException {
        // load Derby driver
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException ex) {
            throw ex;
        }
        
    }    
     /**
      * This method will create the tables of ImgTagconter
      * @param dbConnection
      * @return boolean if table was created
      * @throws Exception with kind of error if didn't create tables . 
      */
    private boolean createTables(Connection dbConnection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            statement.execute(strCreateAddressTable);
            bCreatedTables = true;
        } catch (SQLException ex) {
        }        
        return bCreatedTables;
     }     
    /**
     * This method will connect to the database creating statements to queries
     * get/set and update for example.
     * @throws SQLException If didn't connect.
     */
    public void connect() throws SQLException{
        String dbUrl = getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            stmtSaveNewRecord = dbConnection.prepareStatement(strSaveUrl, Statement.RETURN_GENERATED_KEYS);
            dbConnection.prepareStatement(strUpdateUrl);
            stmtGetUrl = dbConnection.prepareStatement(strGetUrl);
            stmtDeleteUrl = dbConnection.prepareStatement(strDeleteUrl);            
            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            //if got exeption and the reason is that url already exist
            //reconect to data base anyway, because it's created 
            if(ex.getErrorCode() == 30000)
            {
                createTables(dbConnection);
                connect();
            }
            else
                throw new SQLException("Couldn't connect to database.");
        }
    }
    /**
     * This method will disconnect from the database
     * @throws Exception with kind of error if didn't disconnected
     **/
    public void disconnect(){
        if(isConnected) {
            String dbUrl = getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
        }
    }
    /**
     * This method will get the database URL
     * @return String with database url
     **/
    public String getDatabaseUrl() {
        String dbUrl = dbProperties.getProperty("derby.url") + dbName;
        return dbUrl;
    }
    /**
     * This method build list and fill it with all entries
     * @return list with all entried
     * @throws Exception with kind of error if didn't get entries
     **/
    public List<ListEntry> getListEntries() throws SQLException{
        List<ListEntry> listEntries = new ArrayList<ListEntry>();
        Statement queryStatement = null;
        ResultSet results = null;
        
        try {
            queryStatement = dbConnection.createStatement();
            results = queryStatement.executeQuery(strGetListEntries);
            while(results.next()) {
                String Url = results.getString(1);
                int numberOfPictures = results.getInt(2);
                ListEntry entry = new ListEntry(numberOfPictures, Url);
                listEntries.add(entry);
            }
            
        } catch (SQLException sqle) {
            throw sqle;
            
        }
        //return list of entries
        return listEntries;
    }
     
  /**
   * Database name
  */     
 private String dbName; 
   /**
   * Connection statement
  */
 private Connection dbConnection;
   /**
   * Proterties of database
  */
 private Properties dbProperties;
   /**
   * boolean which represen if DB is connected
  */
 private boolean isConnected;
   /**
   * Statement of database to get some URL
  */
 private PreparedStatement stmtGetUrl; 
   /**
   * Statement of database to save new record
  */
 private PreparedStatement stmtSaveNewRecord;
   /**
   * Statement of database update some record
  */
 private PreparedStatement stmtUpdateExistingRecord; 
   /**
   * Statement of database get all entries
  */
 private PreparedStatement stmtGetListEntries; 
   /**
   * Statement of database to delete record
  */
 private PreparedStatement stmtDeleteUrl;
 /**Strings from database that will be queried   
  * creation.
  */
 private static final String strCreateAddressTable =
            "create table APP.PAGES (" +
            "    URL               VARCHAR(256) NOT NULL PRIMARY KEY," +
            "    NUMBERPICTURES    NUMERIC(20) " +
            ")";
  /**Strings from database that will be queried   
  * get some Url.
  */
 private static final String strGetUrl =
            "SELECT * FROM APP.PAGES " +
            "WHERE URL = ?";
  /**Strings from database that will be queried   
  * save.
  */
private static final String strSaveUrl =
            "INSERT INTO APP.PAGES " +
            "(URL, NUMBERPICTURES) " +
            "VALUES (?, ?)";
 /**Strings from database that will be queried   
  * update.
  */
 private static final String strUpdateUrl =
            "UPDATE APP.PAGES " +
            "SET NUMBERPICTURES = ? " +
            "WHERE URL = ?";
  /**Strings from database that will be queried   
  * delete.
  */
 private static final String strDeleteUrl =
            "DELETE FROM APP.PAGES " +
            "WHERE URL = ?";
  /**Strings from database that will be queried   
  * get entries.
  */
 private static final String strGetListEntries =
            "SELECT URL, NUMBERPICTURES FROM APP.PAGES "  +
            "ORDER BY NUMBERPICTURES ASC";
 
}
